package cn.fintecher.pangolin.service.dataimp.service;

import cn.fintecher.pangolin.common.enums.*;
import cn.fintecher.pangolin.common.exception.BadRequestException;
import cn.fintecher.pangolin.common.model.TaskBoxModel;
import cn.fintecher.pangolin.common.model.UploadFile;
import cn.fintecher.pangolin.common.model.UserModel;
import cn.fintecher.pangolin.common.utils.Constants;
import cn.fintecher.pangolin.common.utils.ZWDateUtil;
import cn.fintecher.pangolin.entity.mysql.*;
import cn.fintecher.pangolin.service.dataimp.repository.DepartmentRepository;
import cn.fintecher.pangolin.service.dataimp.repository.SysParamRepository;
import cn.fintecher.pangolin.service.dataimp.repository.UserRepository;
import cn.fintecher.pangolin.service.dataimp.request.ExportUserRequest;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.joda.time.format.DateTimeFormat;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Async;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.io.*;
import java.util.*;

/**
 * @Auther: xiaqun
 * @Description:
 * @Date: 14:16 2019/9/25
 */

@Service("userExportService")
@Transactional(rollbackFor = Exception.class)
public class UserExportService {
    private Logger log = LoggerFactory.getLogger(UserExportService.class);

    @Autowired
    private BaseService baseService;

    @Autowired
    private UserRepository userRepository;

    @Autowired
    private SysParamRepository sysParamRepository;

    @Autowired
    private DepartmentRepository departmentRepository;

    /**
     * 导出人员花名册
     */
    @Async
    public void exportUserList(ExportUserRequest request, String token) {
        UserModel userModel = baseService.getUserByToken(token);
        TaskBoxModel model = baseService.getTaskBox(TaskBoxType.EXPORT, "花名册导出", userModel);
        //获取模板
        try {
            UploadFile uploadFile = downloadUserTemplate();
            InputStream in = baseService.getFile(uploadFile.getId());
            //复制一份模板
            File file = new File(baseService.filePath + "/" + DateTime.now().toDate().getTime() + ".xlsx");
            baseService.copyFile(in, file);
            InputStream inputStream = null;
            OutputStream out = null;
            try {
                inputStream = new FileInputStream(file);
                XSSFWorkbook xssfWorkbook = new XSSFWorkbook(inputStream);
                XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
                List<Object[]> users;
                if (Objects.isNull(request.getIds()) || request.getIds().isEmpty()) {
                    users = userRepository.exportUser();
                } else {
                    users = userRepository.exportUser(request.getIds());
                }
                if (!users.isEmpty()) {
                    int rowNum = 2;
                    for (Object[] user : users) {
                        Row row = sheet.createRow(rowNum);
                        for (int i = 0; i < user.length; i++) {
                            Cell cell = row.createCell(i);
                            cell.setCellValue((Objects.nonNull(user[i]) ? user[i].toString() : ""));
                        }
                        rowNum++;
                    }
                }
                out = new FileOutputStream(file);
                xssfWorkbook.write(out);
                UploadFile uploadFile1 = baseService.uploadExcel(file);
                baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FINISHED, uploadFile1.getId(), uploadFile1.getFileName(), userModel, null, ZWDateUtil.getNowDateTime(), "花名册导出");
            } catch (Exception e) {
                log.error(e.getMessage(), e);
                baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FAILURE, null, null, userModel, null, ZWDateUtil.getNowDateTime(), "花名册导出");
            } finally {
                try {
                    if (inputStream != null) {
                        inputStream.close();
                    }
                } catch (Exception e) {
                    log.error("关闭输入流错误！");
                }
                try {
                    if (out != null) {
                        out.close();
                    }
                } catch (Exception e) {
                    log.error("关闭输出流错误！");
                }
            }
        } catch (BadRequestException e1) {
            log.error(e1.getMessage(), e1);
            UploadFile uploadFile = baseService.generateTxtFile(Collections.singletonList("系统参数中花名册导入模板ID已失效,请重新上传新的模板文件"), ZWDateUtil.fomratterDate(new Date(), "yyyyMMdd"));
            baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FAILURE, uploadFile.getId(), "花名册导出", userModel, null, ZWDateUtil.getNowDateTime(), "花名册导出");
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            baseService.sendTaskBoxMessage(model, TaskBoxType.EXPORT, TaskBoxStatus.FAILURE, null, null, userModel, null, ZWDateUtil.getNowDateTime(), "花名册导出");
        }
    }

    /**
     * 下载导入花名册模板
     */
    public UploadFile downloadUserTemplate() {
        SysParam sysParam = sysParamRepository.findOne(QSysParam.sysParam.code.eq("user.template")).orElseThrow(() -> new BadRequestException(null, "sysParam", "sysParam.is.null"));
        if (sysParam.getStatus().equals(BaseStatus.OFF)) {
            throw new BadRequestException(null, "sysParam", "sysParam.is.off");
        }
        if (StringUtils.isBlank(sysParam.getValue())) {
            throw new BadRequestException(null, "sysParam", "sysParam.value.is.null");
        }
        try {
            return baseService.getUploadFile(sysParam.getValue());
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            throw new BadRequestException(null, "template", "template.is.overtime1");
        }
    }

    /**
     * 导入花名册
     */
    @Async
    public void uploadUser(String fileId, String token) throws IOException {
        UserModel userModel = baseService.getUserByToken(token);
        TaskBoxModel model = baseService.getTaskBox(TaskBoxType.IMPORT, "花名册导入", userModel);
        InputStream in = baseService.getFile(fileId);
        List<String> errors = new ArrayList<>();
        List<User> users = new ArrayList<>();
        SysParam sysParam = sysParamRepository.findOne(QSysParam.sysParam.code.eq(Constants.DEFAULT_PASSWORD)).orElseThrow(() -> new BadRequestException(null, "sysParam", "sysparam.is.null"));
        if (Objects.equals(sysParam.getStatus(), BaseStatus.OFF)) {
            errors.add("系统参数【默认密码】未启用");
        }
        if (org.apache.commons.lang3.StringUtils.isBlank(sysParam.getValue())) {
            errors.add("系统参数【默认密码】值为空");
        }
        BCryptPasswordEncoder passwordEncoder = new BCryptPasswordEncoder();
        try {
            XSSFWorkbook xssfWorkbook = new XSSFWorkbook(in);
            XSSFSheet sheet = xssfWorkbook.getSheetAt(0);
            for (int i = 2; i < sheet.getLastRowNum() + 1; i++) {
                Row row = sheet.getRow(i);
                User user = generateUser(row, errors, sysParam.getValue(), passwordEncoder, userModel);
                users.add(user);
            }
            if (users.isEmpty()) {
                UploadFile uploadFile = baseService.generateTxtFile(Collections.singletonList("模板中没有数据"), Long.toString(ZWDateUtil.getNowDateTime().getTime()));
                baseService.sendTaskBoxMessage(model, TaskBoxType.IMPORT, TaskBoxStatus.FAILURE, uploadFile.getId(), uploadFile.getFileName(), userModel, null, ZWDateUtil.getNowDateTime(), "花名册导入");
                return;
            }
            if (!errors.isEmpty()) {
                UploadFile uploadFile = baseService.generateTxtFile(errors, Long.toString(ZWDateUtil.getNowDateTime().getTime()));
                baseService.sendTaskBoxMessage(model, TaskBoxType.IMPORT, TaskBoxStatus.FAILURE, uploadFile.getId(), uploadFile.getFileName(), userModel, null, ZWDateUtil.getNowDateTime(), "花名册导入");
            } else {
                userRepository.saveAll(users);
                baseService.sendTaskBoxMessage(model, TaskBoxType.IMPORT, TaskBoxStatus.FINISHED, null, null, userModel, null, ZWDateUtil.getNowDateTime(), "花名册导入");
            }
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            UploadFile uploadFile = baseService.generateTxtFile(Collections.singletonList("花名册模板有误"), Long.toString(ZWDateUtil.getNowDateTime().getTime()));
            baseService.sendTaskBoxMessage(model, TaskBoxType.IMPORT, TaskBoxStatus.FAILURE, uploadFile.getId(), uploadFile.getFileName(), userModel, null, ZWDateUtil.getNowDateTime(), "花名册导入");
        } finally {
            if (in != null) {
                in.close();
            }
        }
    }

    /**
     * 生成用户对象
     */
    private User generateUser(Row row, List<String> errors, String password, BCryptPasswordEncoder passwordEncoder, UserModel userModel) {
        User user = new User();
        user.setIsCheck(BaseSelect.NO);
        user.setIsSystem(BaseSelect.NO);
        String userName = setCellType(row, 0).getStringCellValue(); //用户名
        if (StringUtils.isBlank(userName)) {
            String error = "第【" + (row.getRowNum() + 1) + "】行【员工工号】为空";
            errors.add(error);
        } else {
            user.setUsername(StringUtils.trim(userName));
        }
        String realName = setCellType(row, 1).getStringCellValue(); //姓名
        if (StringUtils.isBlank(realName)) {
            String error = "第【" + (row.getRowNum() + 1) + "】行【姓名】为空";
            errors.add(error);
        } else {
            user.setRealName(StringUtils.trim(realName));
        }
        user.setPassword(passwordEncoder.encode(password)); //密码
        user.setStation(setCellType(row, 2).getStringCellValue()); //职务
        String isManage = setCellType(row, 3).getStringCellValue(); //是否管理员
        if (StringUtils.isBlank(isManage)) {
            String error = "第【" + (row.getRowNum() + 1) + "】行【是否管理人员】为空";
            errors.add(error);
        } else {
            switch (StringUtils.trim(isManage)) {
                case "是":
                    user.setIsManage(BaseSelect.YES);
                    break;
                case "否":
                    user.setIsManage(BaseSelect.NO);
                    break;
                default:
                    String error = "第【" + (row.getRowNum() + 1) + "】行【是否管理人员】无法识别,该字段只能选填'是'、'否'";
                    errors.add(error);
                    break;
            }
        }
        String status = setCellType(row, 4).getStringCellValue(); //状态
        if (StringUtils.isBlank(status)) {
            String error = "第【" + (row.getRowNum() + 1) + "】行【状态】为空";
            errors.add(error);
        } else {
            switch (StringUtils.trim(status)) {
                case "在职":
                    user.setStatus(UserStatus.WORKING);
                    break;
                case "离职":
                    user.setStatus(UserStatus.LEAVE);
                    break;
                case "锁定":
                    user.setStatus(UserStatus.LOCKING);
                    break;
                default:
                    String error = "第【" + (row.getRowNum() + 1) + "】行【状态】无法识别,该字段只能选填'在职'、'离职'、'锁定'";
                    errors.add(error);
                    break;
            }
        }
        user.setLockNumber(0); //锁定次数
        String departName = setCellType(row, 5).getStringCellValue(); //所属机构
        if (StringUtils.isBlank(departName)) {
            String error = "第【" + (row.getRowNum() + 1) + "】行【所属机构】为空";
            errors.add(error);
        } else {
            Department department = departmentRepository.findByName(StringUtils.trim(departName));
            if (Objects.isNull(department)) {
                String error = "第【" + (row.getRowNum() + 1) + "】行【所属机构】所填写的机构名称在系统中不存在";
                errors.add(error);
            } else {
                user.setDepartment(department);
            }
        }

        //用户详情
        UserDetail userDetail = new UserDetail();
        userDetail.setCertificateNo(StringUtils.trim(setCellType(row, 6).getStringCellValue())); //证件号码
        String sex = setCellType(row, 7).getStringCellValue(); //性别
        if (StringUtils.isNotBlank(sex)) {
            switch (StringUtils.trim(sex)) {
                case "男":
                    userDetail.setSex(Sex.MALE);
                    break;
                case "女":
                    userDetail.setSex(Sex.FEMALE);
                    break;
                case "未知":
                    userDetail.setSex(Sex.UNKNOWN);
                    break;
                default:
                    String error = "第【" + (row.getRowNum() + 1) + "】行【性别】无法识别,该字段只能选填'男'、'女'、'未知'";
                    errors.add(error);
                    break;
            }
        }
        String age = setCellType(row, 8).getStringCellValue(); //年龄
        if (StringUtils.isNotBlank(age)) {
            try {
                Integer age1 = Integer.parseInt(StringUtils.trim(age));
                userDetail.setAge(age1);
            } catch (Exception e) {
                String error = "第【" + (row.getRowNum() + 1) + "】行【年龄】无法识别,该字段只能为数字";
                errors.add(error);
            }
        }
        String birthplace = setCellType(row, 9).getStringCellValue(); //籍贯
        userDetail.setBirthplace(StringUtils.isNotBlank(birthplace) ? birthplace : null);
        String nation = setCellType(row, 10).getStringCellValue(); //民族
        userDetail.setNation(StringUtils.isNotBlank(nation) ? nation : null);
        String politicalStatus = setCellType(row, 11).getStringCellValue(); //政治面貌
        userDetail.setNation(StringUtils.isNotBlank(politicalStatus) ? politicalStatus : null);
        String maritalStatus = setCellType(row, 12).getStringCellValue(); //婚姻状况
        if (StringUtils.isNotBlank(maritalStatus)) {
            switch (StringUtils.trim(maritalStatus)) {
                case "未婚":
                    userDetail.setMaritalStatus(Marital.UNMARRIED);
                    break;
                case "已婚":
                    userDetail.setMaritalStatus(Marital.MARRIED);
                    break;
                case "未知":
                    userDetail.setMaritalStatus(Marital.UNKNOWN);
                    break;
                default:
                    String error = "第【" + (row.getRowNum() + 1) + "】行【婚姻状况】无法识别,该字段只能选填'未婚'、'已婚'、'未知'";
                    errors.add(error);
                    break;
            }
        }
        String education = setCellType(row, 13).getStringCellValue(); //学历
        userDetail.setEducation(StringUtils.isNotBlank(education) ? education : null);
        String profession = setCellType(row, 14).getStringCellValue(); //专业
        userDetail.setNation(StringUtils.isNotBlank(profession) ? profession : null);
        String school = setCellType(row, 15).getStringCellValue(); //毕业学校
        userDetail.setNation(StringUtils.isNotBlank(school) ? school : null);
        String graduationTime = setCellType(row, 16).getStringCellValue(); //毕业时间
        if (StringUtils.isNotBlank(graduationTime)) {
            Date date = baseService.transferDate(graduationTime);
            if (Objects.isNull(date)) {
                String error = "第【" + (row.getRowNum() + 1) + "】行【毕业时间】无法识别,该字段格式只能为'1970-01-01'、'1970/01/01'、'19700101'";
                errors.add(error);
            } else {
                userDetail.setGraduationTime(date);
            }
        }
        String joinWorkTime = setCellType(row, 17).getStringCellValue(); //参加工作时间
        if (StringUtils.isNotBlank(joinWorkTime)) {
            Date date = baseService.transferDate(joinWorkTime);
            if (Objects.isNull(date)) {
                String error = "第【" + (row.getRowNum() + 1) + "】行【参加工作时间】无法识别,该字段格式只能为'1970-01-01'、'1970/01/01'、'19700101'";
                errors.add(error);
            } else {
                userDetail.setJoinWorkTime(date);
            }
        }
        String phoneNo = setCellType(row, 18).getStringCellValue(); //座机号
        userDetail.setPhoneNo(StringUtils.isNotBlank(phoneNo) ? phoneNo : null);
        String mobileNo = setCellType(row, 19).getStringCellValue(); //手机
        userDetail.setMobileNo(StringUtils.isNotBlank(mobileNo) ? mobileNo : null);
        String email = setCellType(row, 20).getStringCellValue(); //邮箱
        userDetail.setEmail(StringUtils.isNotBlank(email) ? email : null);
        String experience = setCellType(row, 21).getStringCellValue(); //催收经验
        userDetail.setExperience(StringUtils.isNotBlank(experience) ? experience : null);
        String lastEmployer = setCellType(row, 22).getStringCellValue(); //前工作单位
        userDetail.setLastEmployer(StringUtils.isNotBlank(lastEmployer) ? lastEmployer : null);
        String emergencyContactName = setCellType(row, 23).getStringCellValue(); //紧急联系人姓名
        userDetail.setEmergencyContactName(StringUtils.isNotBlank(emergencyContactName) ? emergencyContactName : null);
        String emergencyContactNo = setCellType(row, 24).getStringCellValue(); //紧急联系人电话
        userDetail.setEmergencyContactNo(StringUtils.isNotBlank(emergencyContactNo) ? emergencyContactNo : null);
        String homeAddress = setCellType(row, 25).getStringCellValue(); //家庭住址
        userDetail.setHomeAddress(StringUtils.isNotBlank(homeAddress) ? homeAddress : null);
        String residenceAddress = setCellType(row, 26).getStringCellValue(); //户籍地址
        userDetail.setResidenceAddress(StringUtils.isNotBlank(residenceAddress) ? residenceAddress : null);
        String entryTime = setCellType(row, 27).getStringCellValue(); //入职时间
        if (StringUtils.isNotBlank(entryTime)) {
            Date date = baseService.transferDate(entryTime);
            if (Objects.isNull(date)) {
                String error = "第【" + (row.getRowNum() + 1) + "】行【入职时间】无法识别,该字段格式只能为'1970-01-01'、'1970/01/01'、'19700101'";
                errors.add(error);
            } else {
                userDetail.setEntryTime(date);
            }
        } else {
            String error = "第【" + (row.getRowNum() + 1) + "】行【入职时间】为空";
            errors.add(error);
        }
        String workAge = setCellType(row, 28).getStringCellValue(); //本单位工龄
        userDetail.setWorkAge(StringUtils.isNotBlank(workAge) ? workAge : null);
        String insurance = setCellType(row, 29).getStringCellValue(); //保险办理
        userDetail.setInsurance(StringUtils.isNotBlank(insurance) ? insurance : null);
        String bank = setCellType(row, 30).getStringCellValue(); //工资银行
        userDetail.setBank(StringUtils.isNotBlank(bank) ? bank : null);
        String bankCard = setCellType(row, 31).getStringCellValue(); //工资卡号
        userDetail.setBankCard(StringUtils.isNotBlank(bankCard) ? bankCard : null);
        String firstSignDate = setCellType(row, 32).getStringCellValue(); //第一次合同签订日
        if (StringUtils.isNotBlank(firstSignDate)) {
            Date date = baseService.transferDate(firstSignDate);
            if (Objects.isNull(date)) {
                String error = "第【" + (row.getRowNum() + 1) + "】行【第一次合同签订日】无法识别,该字段格式只能为'1970-01-01'、'1970/01/01'、'19700101'";
                errors.add(error);
            } else {
                userDetail.setFirstSignDate(date);
            }
        }
        String secondSignDate = setCellType(row, 33).getStringCellValue(); //第二次合同签订日
        if (StringUtils.isNotBlank(secondSignDate)) {
            Date date = baseService.transferDate(secondSignDate);
            if (Objects.isNull(date)) {
                String error = "第【" + (row.getRowNum() + 1) + "】行【第二次合同签订日】无法识别,该字段格式只能为'1970-01-01'、'1970/01/01'、'19700101'";
                errors.add(error);
            } else {
                userDetail.setSecondSignDate(date);
            }
        }
        String thirdSignDate = setCellType(row, 34).getStringCellValue(); //第三次合同签订日
        if (StringUtils.isNotBlank(thirdSignDate)) {
            Date date = baseService.transferDate(thirdSignDate);
            if (Objects.isNull(date)) {
                String error = "第【" + (row.getRowNum() + 1) + "】行【第三次合同签订日】无法识别,该字段格式只能为'1970-01-01'、'1970/01/01'、'19700101'";
                errors.add(error);
            } else {
                userDetail.setThirdSignDate(date);
            }
        }
        String firstExpireDate = setCellType(row, 35).getStringCellValue(); //第一次合同到期日
        if (StringUtils.isNotBlank(firstExpireDate)) {
            Date date = baseService.transferDate(firstExpireDate);
            if (Objects.isNull(date)) {
                String error = "第【" + (row.getRowNum() + 1) + "】行【第一次合同到期日】无法识别,该字段格式只能为'1970-01-01'、'1970/01/01'、'19700101'";
                errors.add(error);
            } else {
                userDetail.setFirstExpireDate(date);
            }
        }
        String secondExpireDate = setCellType(row, 36).getStringCellValue(); //第二次合同到期日
        if (StringUtils.isNotBlank(secondExpireDate)) {
            Date date = baseService.transferDate(secondExpireDate);
            if (Objects.isNull(date)) {
                String error = "第【" + (row.getRowNum() + 1) + "】行【第二次合同到期日】无法识别,该字段格式只能为'1970-01-01'、'1970/01/01'、'19700101'";
                errors.add(error);
            } else {
                userDetail.setSecondExpireDate(date);
            }
        }
        String leaveDate = setCellType(row, 37).getStringCellValue(); //离职日期
        if (StringUtils.isNotBlank(leaveDate)) {
            Date date = baseService.transferDate(leaveDate);
            if (Objects.isNull(date)) {
                String error = "第【" + (row.getRowNum() + 1) + "】行【离职日期】无法识别,该字段格式只能为'1970-01-01'、'1970/01/01'、'19700101'";
                errors.add(error);
            } else {
                userDetail.setLeaveDate(date);
            }
        }
        String leaveReason = setCellType(row, 38).getStringCellValue(); //离职原因
        userDetail.setLeaveReason(StringUtils.isNotBlank(leaveReason) ? leaveReason : null);
        String remark = setCellType(row, 39).getStringCellValue(); //备注
        userDetail.setBank(StringUtils.isNotBlank(remark) ? remark : null);
        userDetail.process(userModel.getId(), ZWDateUtil.getNowDateTime(), true);

        user.setUserDetail(userDetail);
        user.process(userModel.getId(), ZWDateUtil.getNowDateTime(), true);
        return user;
    }

    /**
     * 设置单元格格式
     */
    private Cell setCellType(Row row, int i) {
        Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
        cell.setCellType(CellType.STRING);
        return cell;
    }
}
